/*
 * 描述:根据word中的属性域定义生成gdb中的属性域,根据excel中的图层定义生成gdb的相应图层
 * 作者:wx
 * 时间:20161108
 */
package com.ao.action;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.net.UnknownHostException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;

import com.ao.entity.*;
import com.ao.utils.ConnectGDB;
import com.ao.utils.InitAOLicense;
import com.esri.arcgis.datasourcesGDB.AccessWorkspaceFactory;
import com.esri.arcgis.datasourcesGDB.FileGDBWorkspaceFactory;
import com.esri.arcgis.geodatabase.CodedValueDomain;
import com.esri.arcgis.geodatabase.FeatureClass;
import com.esri.arcgis.geodatabase.FeatureClassDescription;
import com.esri.arcgis.geodatabase.Field;
import com.esri.arcgis.geodatabase.Fields;
import com.esri.arcgis.geodatabase.GeometryDef;
import com.esri.arcgis.geodatabase.IClassSchemaEdit;
import com.esri.arcgis.geodatabase.IClassSchemaEdit3;
import com.esri.arcgis.geodatabase.IClassSchemaEdit3Proxy;
import com.esri.arcgis.geodatabase.ICodedValueDomain;
import com.esri.arcgis.geodatabase.IDataset;
import com.esri.arcgis.geodatabase.IDomain;
import com.esri.arcgis.geodatabase.IEnumDataset;
import com.esri.arcgis.geodatabase.IEnumFeatureClass;
import com.esri.arcgis.geodatabase.IFeatureClass;
import com.esri.arcgis.geodatabase.IFeatureClassDescription;
import com.esri.arcgis.geodatabase.IFeatureDataset;
import com.esri.arcgis.geodatabase.IFeatureWorkspace;
import com.esri.arcgis.geodatabase.IFeatureWorkspaceProxy;
import com.esri.arcgis.geodatabase.IField;
import com.esri.arcgis.geodatabase.IFieldEdit;
import com.esri.arcgis.geodatabase.IFields;
import com.esri.arcgis.geodatabase.IFieldsEdit;
import com.esri.arcgis.geodatabase.IGeometryDef;
import com.esri.arcgis.geodatabase.IGeometryDefEdit;
import com.esri.arcgis.geodatabase.IObjectClassDescription;
import com.esri.arcgis.geodatabase.IWorkspace;
import com.esri.arcgis.geodatabase.IWorkspaceDomains;
import com.esri.arcgis.geodatabase.IWorkspaceDomainsProxy;
import com.esri.arcgis.geodatabase.IWorkspaceFactory;
import com.esri.arcgis.geodatabase.Workspace;
import com.esri.arcgis.geodatabase.esriDatasetType;
import com.esri.arcgis.geodatabase.esriFeatureType;
import com.esri.arcgis.geodatabase.esriFieldType;
import com.esri.arcgis.geodatabase.esriMergePolicyType;
import com.esri.arcgis.geodatabase.esriSplitPolicyType;
import com.esri.arcgis.geometry.IProjection;
import com.esri.arcgis.geometry.ISpatialReference;
import com.esri.arcgis.geometry.ISpatialReferenceFactory;
import com.esri.arcgis.geometry.SpatialReferenceEnvironment;
import com.esri.arcgis.geometry.esriGeometryType;
import com.esri.arcgis.system.ISet;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class Create_GDB_From_Office {
	public static JSONObject doaminNameDesc = new JSONObject();//存储属性域的名称和描述
	public static Map<String,Integer> layerNameGeoType = new HashMap<String, Integer>();//存储图层的名称几何类型(点线面)
	public static Map<String,LayerInfo> layersInfo = new HashMap<String, LayerInfo>();//存储图层要导入GDB的图层信息
	public static void main(String[] args) throws Exception {
		InitAOLicense.init();
		getGeometryDefFromMDB("D:\\excel2gdb\\299标准库.mdb");
		createFeatureClassByExcel("D:\\excel2gdb\\1.xlsx","D:\\excel2gdb\\jxtest.gdb","jxbj");
//		createFeatureClassByExcel("D:\\excel2gdb\\2.xlsx","D:\\excel2gdb\\jxtest.gdb","jxbj");
//		createFeatureClassByExcel("D:\\excel2gdb\\3.xlsx","D:\\excel2gdb\\jxtest.gdb","jxbj");
//		createFeatureClassByExcel("D:\\excel2gdb\\4.xlsx","D:\\excel2gdb\\jxtest.gdb","jxbj");
//		createFeatureClassByExcel("D:\\excel2gdb\\5.xlsx","D:\\excel2gdb\\jxtest.gdb","jxbj");
//		createFeatureClassByExcel("D:\\excel2gdb\\6.xlsx","D:\\excel2gdb\\jxtest.gdb","jxbj");
//		createFeatureClassByExcel("D:\\excel2gdb\\7.xlsx","D:\\excel2gdb\\jxtest.gdb","jxbj");
		assignDomainAndAlterAliasName("D:\\excel2gdb\\jxtest.gdb","jxbj");
	}
	
	//读取word文档并创建属性域
	public static void readWordAndCreateDomain() throws Exception{
		InitAOLicense.init();
		IWorkspace workspace = ConnectGDB.openFromFile_fGDB_Workspace("D:\\excel2gdb\\jxtest.gdb");
		Map<String,JSONArray> doaminMap = readWord("D:\\excel2gdb\\bbbb.docx");
		addCodedValueDomain(workspace,doaminMap);
	}
	
	//读取word,获取属性域的code和value
	//Map<String,JSONArray>存储tc_Belo_Rive属性域和已JsonObject存储的code,value
	//
	private static Map<String,JSONArray> readWord(String filepath) throws IOException{
		Map<String,JSONArray> domainMap = new HashMap<String,JSONArray>();
		FileInputStream fis = new FileInputStream(filepath);
		XWPFDocument doc = new XWPFDocument(fis);
		
		List<XWPFParagraph> paras = doc.getParagraphs();
		List<XWPFTable> tables = doc.getTables();
		
		for(int i=0;i<paras.size();i++){
			JSONArray domainArray = new JSONArray();
			XWPFParagraph para = paras.get(i);
			
			String paraStr = para.getText();
			String subStr = "";//属性域名称
			String descStr = "";//属性域描述
			
			if(paraStr.length()>1){
				if(paraStr.contains("(")){
					subStr = paraStr.substring(paraStr.indexOf("(")+1, paraStr.indexOf(")"));
					descStr = paraStr.substring(0,paraStr.indexOf("("));
				}else if(paraStr.contains("（")){
					subStr = paraStr.substring(paraStr.indexOf("（")+1, paraStr.indexOf("）"));
					descStr = paraStr.substring(0,paraStr.indexOf("（"));
				}
				
//				System.out.println(subStr+":"+descStr);
				doaminNameDesc.put(subStr, descStr);
				
				XWPFTable table = tables.get(i);
				List<XWPFTableRow> rows = table.getRows();
				for(int j=1;j<rows.size();j++){
					JSONObject domain = new JSONObject();
					XWPFTableRow row = rows.get(j);
					String code = row.getTableCells().get(0).getText();//属性域编码
					String value = row.getTableCells().get(1).getText();//属性域值
//					System.out.println(code+":"+value);
					domain.put(code, value);
					domainArray.add(domain);
				}
				domainMap.put(subStr, domainArray);
			}
			System.out.println("---------------------------------");
		}
		
		doc.close();
		fis.close();
		return domainMap;
	}
	
	//将读取完成的word转换成gdb中的属性域
	static void addCodedValueDomain(IWorkspace workspace,Map<String,JSONArray> domainMap)throws Exception
	{	
		// Cast the workspace to the IWorkspaceDomains interface.
		IWorkspaceDomains workspaceDomains = (IWorkspaceDomains)workspace;
		
		Set<String> mapKeySet = domainMap.keySet();
		Iterator<String> mapKey = mapKeySet.iterator();
		while(mapKey.hasNext()){
			String key = mapKey.next();
			JSONArray codeValueArray = domainMap.get(key);
			ICodedValueDomain codedValueDomain = new CodedValueDomain();
			System.out.println("属性域"+key+"包括:");
			for(int j=0;j<codeValueArray.size();j++){
				JSONObject temp = (JSONObject) codeValueArray.get(j);
				String code =  String.valueOf(j+1);
//				if((String)temp.get(code) == null){
//					System.out.println();
//				}
				codedValueDomain.addCode(code, (String)temp.get(code));
				System.out.println("code:"+code+";value:"+temp.get(code));
			}
			System.out.println("--------------------");
			IDomain domain = (IDomain)codedValueDomain;
			domain.setName(key);
			domain.setDescription(doaminNameDesc.getString(key));
			domain.setFieldType(esriFieldType.esriFieldTypeString);
			domain.setSplitPolicy(esriSplitPolicyType.esriSPTDuplicate);
			domain.setMergePolicy(esriMergePolicyType.esriMPTDefaultValue);
			workspaceDomains.addDomain(domain);
		}
	}
	
	
	/**
	 * 从mdb数据库读取图层的几何类型,赋值给layerNameGeoType
	 * 0,esriGeometryNull
	 * 1,esriGeometryPoint
	 * 2,esriGeometryMultipoint
	 * 3,esriGeometryPolyline
	 * 4,esriGeometryPolygon
	 * */
	public static void getGeometryDefFromMDB(String mdbPath) throws UnknownHostException, IOException{
		IWorkspaceFactory MDBworkspaceFactory = new AccessWorkspaceFactory();
		Workspace MDBworkspace = new Workspace(MDBworkspaceFactory.openFromFile(mdbPath, 0));
		IEnumDataset MDBenumDataset = MDBworkspace.getDatasets(esriDatasetType.esriDTFeatureClass);
		IDataset MDBds = MDBenumDataset.next();
		
		while (MDBds != null) {
			FeatureClass MDBfClass = new FeatureClass(MDBds);
			IFields fields = MDBfClass.getFields();
			IField shapeField = fields.getField(fields.findField("SHAPE"));
			IGeometryDef geometryDef = shapeField.getGeometryDef();
			System.out.println(MDBfClass.getName()+":"+geometryDef.getGeometryType());
			layerNameGeoType.put(MDBfClass.getName(),geometryDef.getGeometryType());
			MDBds = MDBenumDataset.next();
		}
		
		System.out.println("图层几何类型读取完毕");
	}
	
	public static void createFeatureClassbyFieldInfo(IWorkspace workspace,IFeatureDataset featureDataset,LayerInfo myLayer) throws UnknownHostException, IOException{
        IFields fields = new Fields();
        IFieldsEdit fieldsEdit = (IFieldsEdit)fields;
        fieldsEdit.setFieldCount( myLayer.getFields().size()+2);
        
        //创建ojbectId字段
        IField fieldUserDefined = new Field();
        IFieldEdit fieldEdit = (IFieldEdit)fieldUserDefined;
        fieldEdit.setName("OBJECTID");
        fieldEdit.setAliasName("OBJECT ID");
        fieldEdit.setType(esriFieldType.esriFieldTypeOID);
        fieldsEdit.setFieldByRef(0, fieldUserDefined);
        
        //创建SHAPE字段
        fieldUserDefined = new Field();
        fieldEdit = (IFieldEdit)fieldUserDefined;
        IGeometryDef geometryDef = new GeometryDef();
        IGeometryDefEdit geometryDefEdit = (IGeometryDefEdit)geometryDef;
        Integer layerGeoType = layerNameGeoType.get(myLayer.getLayerName());
        if(layerGeoType == null){
        	System.out.println(myLayer.getLayerAliasName()+":"+myLayer.getLayerName()+"找不到对应图层");
        }
        switch (layerGeoType) {
		case 1:
			geometryDefEdit.setGeometryType(esriGeometryType.esriGeometryPoint);
			break;
		case 2:
			geometryDefEdit.setGeometryType(esriGeometryType.esriGeometryMultipoint);
			break;
		case 3:
			geometryDefEdit.setGeometryType(esriGeometryType.esriGeometryPolyline);
			break;
		case 4:
			geometryDefEdit.setGeometryType(esriGeometryType.esriGeometryPolygon);
			break;
		}
        geometryDefEdit.setGridCount(1);
        geometryDefEdit.setGridSize(0, 0);
        geometryDefEdit.setHasM(false);
        geometryDefEdit.setHasZ(false);
        fieldEdit.setName("SHAPE");
        fieldEdit.setType(esriFieldType.esriFieldTypeGeometry);
        fieldEdit.setGeometryDefByRef(geometryDef);
        fieldEdit.setIsNullable(true);
        fieldEdit.setRequired(true);
        fieldsEdit.setFieldByRef(1, fieldUserDefined);
        
        //创建excel输入字段
       
        for(int i=0;i< myLayer.getFields().size();i++){
        	FieldInfo tempLayerFieldInfo =  myLayer.getFields().get(i);
            fieldUserDefined = new Field();
            fieldEdit = (IFieldEdit)fieldUserDefined;
            fieldEdit.setName(tempLayerFieldInfo.getName());
            fieldEdit.setAliasName(tempLayerFieldInfo.getAliasName());
            fieldEdit.setEditable(true);
            fieldEdit.setIsNullable(false);
            
            IWorkspaceDomains workspaceDomains = new IWorkspaceDomainsProxy(workspace);
            if(tempLayerFieldInfo.getForeignKey() != null){
            	if(tempLayerFieldInfo.getForeignKey().length() > 0){
            		IDomain domain = workspaceDomains.getDomainByName(tempLayerFieldInfo.getForeignKey());
            		if(domain == null){
            			System.out.println(myLayer.getLayerAliasName()+"的外键"+tempLayerFieldInfo.getForeignKey()+"不存在");
            		}
            	}
            }

            fieldEdit.setDomainByRef(null);
            
            if(tempLayerFieldInfo.getDataType().contains("DATE")){
            	fieldEdit.setType(esriFieldType.esriFieldTypeDate);
            }
            else {
            	fieldEdit.setType(esriFieldType.esriFieldTypeString);
            	fieldEdit.setLength(500);
            }
            
            fieldsEdit.setFieldByRef(i+2, fieldUserDefined);
        }
        
        IFeatureClassDescription fcDesc = new FeatureClassDescription();
        IObjectClassDescription ocDesc = (IObjectClassDescription)fcDesc;
  
        featureDataset.createFeatureClass(myLayer.getLayerName(), fields, ocDesc.getInstanceCLSID(), ocDesc.getClassExtensionCLSID(), esriFeatureType.esriFTSimple, "SHAPE", "");
        System.out.println("建立图层:"+myLayer.getLayerAliasName());
	}
	
	public static void createFeatureClassByExcel(String excelPath,String gdbPath,String featureDateSetName) throws Exception{
		FileInputStream fis =new FileInputStream(excelPath);  
		OPCPackage pkg = OPCPackage.open(fis);
		XSSFWorkbook wb = new XSSFWorkbook(pkg);
		int sheetNum = wb.getNumberOfSheets();
		
		//打开指定gdb工作空间
		FileGDBWorkspaceFactory factory = new FileGDBWorkspaceFactory();
		IWorkspace workspace = factory.openFromFile(gdbPath, 0);
		IFeatureWorkspace featureWorkspace = new IFeatureWorkspaceProxy(workspace);
		IEnumDataset enumDs = workspace.getDatasets(esriDatasetType.esriDTFeatureDataset);
		IDataset ds = null;
		ds = enumDs.next();
		IFeatureDataset featureDataset = null;
		while(ds !=null){
			IFeatureDataset tempfds = featureWorkspace.openFeatureDataset(ds.getName());
			if(ds.getName().equals(featureDateSetName)){
				featureDataset = tempfds;
//				System.out.println("featureDataset"+":"+featureDataset.getName());
				break;
			}
			ds = enumDs.next();
		}
		
		for (int i=0;i<sheetNum;i++) {
			XSSFSheet sheet = wb.getSheetAt(i);
			int rowCount = sheet.getPhysicalNumberOfRows();
			LayerInfo myLayer = new LayerInfo();
			
			//获取图层名称
			XSSFRow row = sheet.getRow(1);//row从0开始
			XSSFCell cell = row.getCell(2);//列从1开始
			myLayer.setLayerName(cell.getStringCellValue().replaceAll(" ", ""));
			String layerName = cell.getStringCellValue().replaceAll(" ", "");
			
			//获取图层别名
			row = sheet.getRow(2);
			cell = row.getCell(2);
			myLayer.setLayerAliasName(cell.getStringCellValue().replaceAll(" ", ""));
			String layerDesc = cell.getStringCellValue().replaceAll(" ", "");
			
			//获取excel记录的字段信息
			List<FieldInfo> fields = new ArrayList<FieldInfo>();
			for(int rowIndex = 6;rowIndex < rowCount; rowIndex++){
				if(sheet.getRow(rowIndex) != null){
					if(sheet.getRow(rowIndex).getCell(2) != null){
						if(sheet.getRow(rowIndex).getCell(2).getStringCellValue().length() > 0){
							FieldInfo field = new FieldInfo();
							field.setName(sheet.getRow(rowIndex).getCell(1).getStringCellValue().replaceAll(" ", ""));
							field.setAliasName(sheet.getRow(rowIndex).getCell(2).getStringCellValue().replaceAll(" ", ""));
							field.setDataType(sheet.getRow(rowIndex).getCell(3).getStringCellValue().replaceAll(" ", ""));
							if(sheet.getRow(rowIndex).getCell(4) != null){
								//先将单元格设置为String类型
								sheet.getRow(rowIndex).getCell(4).setCellType(CellType.STRING);
								field.setLengt(sheet.getRow(rowIndex).getCell(4).getStringCellValue().replaceAll(" ", ""));
							}
							//获取外键
							if(sheet.getRow(rowIndex).getCell(8) != null){
								field.setHasForeignKey(true);
								sheet.getRow(rowIndex).getCell(8).setCellType(CellType.STRING);
								field.setForeignKey(sheet.getRow(rowIndex).getCell(8).getStringCellValue().replaceAll(" ", ""));
							}
//							System.out.println(field);
							fields.add(field);
						}
					}
				}
			}
			myLayer.setFields(fields);
//			System.out.println(layerDesc+"共有"+fields.size()+"个字段");
			layersInfo.put(myLayer.getLayerName(), myLayer);
			createFeatureClassbyFieldInfo(workspace,featureDataset,myLayer);
	    }
		
		wb.close();
		pkg.close();
		fis.close();
	}
	
	//图层建立完毕后修改图层别名和字段属性域
	public static void assignDomainAndAlterAliasName(String gdbPath,String featureDatasetName) throws UnknownHostException, IOException{
		FileGDBWorkspaceFactory factory = new FileGDBWorkspaceFactory();
		IWorkspace workspace = factory.openFromFile(gdbPath, 0);
		IFeatureWorkspace featureWorkspace = new IFeatureWorkspaceProxy(workspace);
		IWorkspaceDomains workspaceDomains = new IWorkspaceDomainsProxy(workspace);
		Set<String> featureClassNames = layersInfo.keySet();
		Iterator<String> it = featureClassNames.iterator();
		while(it.hasNext()){
			String fcsName = it.next();
			IFeatureClass ifc =  featureWorkspace.openFeatureClass(fcsName);
			FeatureClass fc = new FeatureClass(ifc);
			
			//修改别名
			IClassSchemaEdit pClassSchemaEdit = (IClassSchemaEdit)fc;
			pClassSchemaEdit.alterAliasName(layersInfo.get(fcsName).getLayerAliasName());
			System.out.println(layersInfo.get(fcsName).getLayerAliasName());
			
			//修改属性域
			for(int i=0;i<layersInfo.get(fcsName).getFields().size();i++){
				FieldInfo field = layersInfo.get(fcsName).getFields().get(i);
				if(field.getForeignKey().length() > 0){
					IClassSchemaEdit3 classSchemaEdit = new IClassSchemaEdit3Proxy(fc);
					IDomain domain = workspaceDomains.getDomainByName(field.getForeignKey());
					classSchemaEdit.alterDomain(field.getName(), domain);
					System.out.println("字段"+field.getName()+"的属性域是"+domain.getName());
				}
			}
			System.out.println("------------------------------");
		}
	}
}
